﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using ADOX;
using nhCommon;
using nhCore.Server;

namespace nhCore
{

    public class Access : IDB
    {
        #region  数据库文件路径及名称、密码、最大尺寸
        /// <summary>
        ///  数据库文件路径及名称
        /// </summary>        
        public static string AccessPath { get; set; } = AppDomain.CurrentDomain.BaseDirectory + "db.mdb";
        private static string AccessPWD { get; set; } = "ZKNh";

        public static string ConnString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={AccessPath};Persist Security Info=True;Jet OLEDB:Database Password={AccessPWD}";
        //private readonly ulong AccessFileMaxSize = (ulong)2 * 1024 * 1024 * 1024;
        //private readonly FileInfo AccessFile;
        //private bool waitForRestart = false; //等待重新开始，防止处理ACCESS数据库时有数据库访问。
        //private int TimeOffset; //日起始时间
        #endregion
        public static OleDbConnection Con { get; set; }
        private OleDbCommand Com { get; set; }

        /// <summary>
        /// 建立ACCESS数据库连接
        /// </summary>
        /// <param name="path">路径及文件名</param>
        /// <param name="pwd">数据库密码</param>
        private Access()
        {
            Con = new OleDbConnection(ConnString);
            Com = Con.CreateCommand();
        }

        bool IDB.AddConfig(uint address, DbConfig cfg)
        {
            bool result = false;
            string sql = "INSERT INTO Configs(Address,[Index],[Type],Display,Unit,Digits,Statistics,[Max],[Min]) VALUES(@Address,@Index,@Type,@Display,@Unit,@Digits,@Statistics,@Max,@Min)";
            lock (Con)
            {
                try
                {
                    Com.CommandText = sql;
                    Com.Parameters.Clear();
                    _ = Com.Parameters.Add(new OleDbParameter("@Address", address));
                    Com.Parameters.Add(new OleDbParameter("@Index", cfg.Index));
                    Com.Parameters.Add(new OleDbParameter("@Type", SqlNull(cfg.Type)));
                    Com.Parameters.Add(new OleDbParameter("@Display", cfg.Display));
                    Com.Parameters.Add(new OleDbParameter("@Unit", cfg.Unit));
                    Com.Parameters.Add(new OleDbParameter("@Digits", cfg.Digits));
                    Com.Parameters.Add(new OleDbParameter("@Statistics", cfg.Statistics));
                    if (cfg.Max.HasValue)
                    {
                        Com.Parameters.Add(new OleDbParameter("@Max", cfg.Max));
                    }
                    else
                    {
                        Com.Parameters.Add(new OleDbParameter("@Max", DBNull.Value));
                    }

                    if (cfg.Min.HasValue)
                    {
                        Com.Parameters.Add(new OleDbParameter("@Min", cfg.Min));
                    }
                    else
                    {
                        Com.Parameters.Add(new OleDbParameter("@Min", DBNull.Value));
                    }
                    Debug.WriteLine(sql);
                    result = Com.ExecuteNonQuery() == 1;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
            }
            return result;
        }

        bool IDB.AddSensor(DbSensor sensor)
        {
            bool result = false;
            string sql = "INSERT INTO Sensors(Address,Type,Name) VALUES(@Address,@Type,@Name)";
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                Com.Parameters.Add(new OleDbParameter("@Address", sensor.Address));
                Com.Parameters.Add(new OleDbParameter("@Type", sensor.Type));
                Com.Parameters.Add(new OleDbParameter("@Name", sensor.Name));
                try
                {
                    result = Com.ExecuteNonQuery() == 1;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
            }
            return result;
        }

        void IDB.ClearData(uint address)
        {
            string sql = "DELETE FROM Datas WHERE Address = @Address";
            lock (Con)
            {
                Com.Parameters.Clear();
                Com.CommandText = sql;
                _ = Com.Parameters.Add(new OleDbParameter("@Address", address));
                Debug.WriteLine(sql);
                try
                {
                    Com.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
                Com.ExecuteNonQuery();
            }
        }

        public void Open()
        {
            try
            {
                Con.Open();
            }
            catch (Exception)
            {

            }

        }
        void IDB.Close()
        {
            Con.Close();
        }

        bool IDB.DeleteSensor(uint address)
        {
            bool result = false;
            string sql = "DELETE FROM Sensors WHERE Address = @Address";
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                _ = Com.Parameters.Add(new OleDbParameter("@Address", address));
                Debug.WriteLine(sql);
                try
                {
                    Com.Transaction = Con.BeginTransaction();
                    Com.ExecuteNonQuery();
                    sql = "DELETE FROM Configs WHERE Address = @Address";
                    Com.CommandText = sql;
                    Com.ExecuteNonQuery();
                    Com.Transaction.Commit();
                    result = true;
                }
                catch (Exception)
                {
                    result = false;
                    Com.Transaction.Rollback();
                }
            }
            return result;
        }

        /// <summary>
        /// 取对应设备地址的采集仪要素配置
        /// </summary>
        /// <param name="address">设备地址</param>
        /// <returns></returns>
        List<DbConfig> IDB.GetConfigs(uint address)
        {
            List<DbConfig> ls = new List<DbConfig>();
            string sql = "SELECT * FROM configs WHERE Address = @Address";
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                _ = Com.Parameters.Add(new OleDbParameter("@Address", address));
                Debug.WriteLine(sql);
                OleDbDataReader reader = Com.ExecuteReader();
                object v = null;
                while (reader.Read())
                {
                    DbConfig c = new DbConfig()//数据库中的要素
                    {
                        Address = (byte)reader.GetInt32(reader.GetOrdinal("Address")),
                        Index = (byte)reader.GetInt32(reader.GetOrdinal("Index")),
                        Type = reader.GetValue(reader.GetOrdinal("Type")).ToString(),
                        Display = reader.GetString(reader.GetOrdinal("Display")),
                        Unit = reader.GetString(reader.GetOrdinal("Unit")),
                        Digits = reader.GetInt32(reader.GetOrdinal("Digits")),
                        Statistics = NhDb.DbToValue<string>(reader.GetString(reader.GetOrdinal("Statistics"))),
                    };
                    try
                    {
                        string s = reader.GetValue(reader.GetOrdinal("DataType")).ToString();
                        Type type = typeof(Int16);
                        if (s.ToLower() == "int32")
                        {
                            type = typeof(Int32);
                        }
                        else if (s.ToLower() == "floatrreverse")
                        {
                            type = typeof(byte);//代替反向浮点
                        }
                        else if (s.ToLower() == "float")
                        {
                            type = typeof(float);
                        }
                        c.DataType = type;
                    }
                    catch (Exception)
                    {
                    }
                    v = reader.GetValue(reader.GetOrdinal("Max"));
                    // string strMax = (string)v;
                    if (v != DBNull.Value)
                    {
                        //c.Max = Double.Parse((string)v);
                        c.Max = (double?)v;
                    }


                    v = reader.GetValue(reader.GetOrdinal("Min"));
                    if (v != DBNull.Value)
                    {
                        // c.Min = Double.Parse((string)v);
                        c.Min = (double?)v;
                    }
                    ls.Add(c);
                }
                reader.Close();
            }
            return ls;
        }

        /// <summary>
        /// 从数据库中读出所有设备，并以列表返回，内含每个采集仪的设备地址、类型、名称
        /// </summary>
        /// <returns></returns>
        Dictionary<byte, DbSensor> IDB.GetSensors()
        {
            if (!CheckField("sensors", "id"))  //兼容老数据库，支持yunlink
            {
                AddColumnToTable("sensors", "id", "Byte");
            }
            Dictionary<byte, DbSensor> ls = new Dictionary<byte, DbSensor>();
            string sql = "SELECT * FROM sensors ORDER BY Address";
            lock (Con)
            {
                Com.CommandText = sql;
                Debug.WriteLine(sql);
                OleDbDataReader reader = Com.ExecuteReader();
                while (reader.Read())
                {
                    DbSensor s = new DbSensor()
                    {
                        Address = (byte)reader.GetInt32(reader.GetOrdinal("Address")),
                        Type = reader.GetString(reader.GetOrdinal("Type")),
                        Name = reader.GetString(reader.GetOrdinal("Name")),
                        Id = (reader.IsDBNull(reader.GetOrdinal("Id")) ? (byte)0 : reader.GetByte(reader.GetOrdinal("Id")))
                    };
                    ls.Add(s.Address, s);
                }
                reader.Close();
            }
            return ls;
        }

        /// <summary>
        /// 读出设置数据
        /// </summary>
        /// <returns></returns>
        Dictionary<string, string> IDB.GetSetting()
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            string sql = "SELECT * FROM setting";
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                Debug.WriteLine(sql);
                OleDbDataReader reader = Com.ExecuteReader();
                int colValue = reader.GetOrdinal("Value");
                int colKey = reader.GetOrdinal("Key");
                while (reader.Read())
                {
                    dic.Add(reader.GetString(colKey).ToLower(), reader.GetValue(colValue).ToString().ToLower());
                }
                reader.Close();
            }

            if (!dic.ContainsKey("pwd"))
            {
                //不存在key;
                Dictionary<string, string> dicNew = new Dictionary<string, string>
                {
                    { "pwd", "" }
                };
                InsertSetting(dicNew);
                dic.Add("pwd", "");
            }
            return dic;
        }

        public bool InsertSetting(Dictionary<string, string> setting)
        {
            bool rst = false;
            string sql = "Insert into Setting([Key],[Value]) Values(@Key,@Value)";
            lock (Con)
            {
                Com.CommandText = sql;
                foreach (KeyValuePair<string, string> kp in setting)
                {
                    Com.Parameters.Clear();
                    _ = Com.Parameters.Add(new OleDbParameter("@Key", kp.Key));
                    Com.Parameters.Add(new OleDbParameter("@Value", kp.Value));
                    Com.ExecuteNonQuery();
                }
                rst = true;
            }
            return rst;
        }

        bool IDB.ModifySensorName(byte address, string nName)
        {
            bool result = false;
            string sql = "UPDATE Sensors SET Name = @Name WHERE Address = @Address";
            lock (Con)
            {
                Com.Parameters.Clear();
                Com.CommandText = sql;
                _ = Com.Parameters.Add(new OleDbParameter("@Name", nName));
                _ = Com.Parameters.Add(new OleDbParameter("@Address", address));
                Debug.WriteLine(sql);
                result = Com.ExecuteNonQuery() == 1;
            }
            return result;
        }

        bool IDB.ModifySetting(Dictionary<string, string> setting)
        {
            bool result = true;
            lock (Con)
            {
                string sql = "UPDATE Setting SET [Value]=@Value WHERE [Key]=@Key";
                Com.CommandText = sql;
                Com.Transaction = Con.BeginTransaction();
                try
                {
                    foreach (var s in setting)
                    {
                        Com.Parameters.Clear();
                        _ = Com.Parameters.Add(new OleDbParameter("@Value", SqlNull(s.Value)));
                        _ = Com.Parameters.Add(new OleDbParameter("@Key", SqlNull(s.Key)));
                        Com.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.ToString());
                    result = false;
                    Com.Transaction.Rollback();
                }

                if (result)
                    Com.Transaction.Commit();
            }
            return result;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sensor"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <param name="hourOffset"></param>
        /// <returns>字典列表为记录，List为表格</returns>
        ReturnData IDB.QueryDatas(DbSensor sensor, DateTime begin, DateTime end, int hourOffset)
        {
            string qTime = $"DateAdd('h',{-hourOffset},`time`)";
            (var columns, _, var columnIndexs) = GroupColumn(sensor.Configs);
            string sql = $"SELECT Time {columns} FROM datas WHERE Address = @Address AND {qTime}>= @Begin AND {qTime} <= @End ORDER BY `time` DESC";
            OleDbDataReader reader;
            ReturnData returnData;
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                _ = Com.Parameters.Add(new OleDbParameter("@Address", sensor.Address));
                _ = Com.Parameters.Add(new OleDbParameter("@Begin", OleDbType.Date) { Value = begin });
                _ = Com.Parameters.Add(new OleDbParameter("@End", OleDbType.Date) { Value = end });
                reader = Com.ExecuteReader();
                returnData = new ReturnData(reader, columnIndexs);
                reader.Close();
            }
            return returnData;
        }

        /// <summary>
        /// 日报，按小时查询
        /// </summary>
        /// <param name="sensor"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <param name="hourOffset"></param>
        /// <returns></returns>
        ReturnData IDB.QueryReportDay(DbSensor sensor, DateTime begin, DateTime end, int hourOffset)
        {
            string format = "yyyy-MM-dd HH:00:00";
            string qTime = $"FORMAT(DateAdd('h',{-hourOffset},`time`),'{format}')";
            string sql = $"SELECT FORMAT(`Time`,'{format}') AS _Time";

            (var columns, _, var columnIndexs) = GroupColumn(sensor.Configs, true);
            sql += columns;

            sql += $" FROM datas WHERE Address =@Address AND {qTime} >= @Begin AND {qTime} <= @End GROUP BY FORMAT(`Time`,'{format}') ORDER BY FORMAT(`Time`,'{format}') DESC";
            OleDbDataReader reader;
            ReturnData ReturnData;
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                Com.Parameters.Add(new OleDbParameter("@Address", sensor.Address));
                Com.Parameters.Add(new OleDbParameter("@Begin", OleDbType.DBTimeStamp) { Value = begin });
                Com.Parameters.Add(new OleDbParameter("@End", OleDbType.DBTimeStamp) { Value = end });
                reader = Com.ExecuteReader();
                ReturnData = new ReturnData(reader, columnIndexs);
                reader.Close();
            }
            return ReturnData;
        }

        /// <summary>
        /// 月报，按天查询
        /// </summary>
        /// <param name="sensor"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <param name="hourOffset"></param>
        /// <returns></returns>
        ReturnData IDB.QueryReportMonth(DbSensor sensor, DateTime begin, DateTime end, int hourOffset)
        {
            string format = "yyyy-MM-dd 00:00:00";
            string tmpSql = $"(SELECT FORMAT(DateAdd('h',{-hourOffset},Time),'{format}') as tm";
            string sql = $"SELECT FORMAT(`tm`, '{format}') as _Time";
            (var columns, var tmpColumns, var columnIndexs) = GroupColumn(sensor.Configs, true, true);
            sql += columns;
            tmpSql += tmpColumns;

            tmpSql += " FROM datas WHERE Address =@Address)Y";
            sql = $"{sql} FROM {tmpSql} WHERE [tm] >= @Begin And [tm] <= @End GROUP BY FORMAT([tm], '{format}') ORDER BY FORMAT([tm], '{format}') DESC";
            Debug.WriteLine(sql);
            OleDbDataReader reader;
            ReturnData returnData;
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                Com.Parameters.Add(new OleDbParameter("@Address", sensor.Address));
                Com.Parameters.Add(new OleDbParameter("@Begin", OleDbType.DBTimeStamp) { Value = begin });
                Com.Parameters.Add(new OleDbParameter("@End", OleDbType.DBTimeStamp) { Value = end });
                reader = Com.ExecuteReader();
                returnData = new ReturnData(reader, columnIndexs);
                reader.Close();
            }
            return returnData;
        }

        ReturnData IDB.QueryReportYear(DbSensor sensor, DateTime begin, DateTime end, int hourOffset)
        {
            string format = "yyyy-MM-01 00:00:00";
            string tmpSql = $"(SELECT FORMAT(DateAdd('h',{-hourOffset},Time),'{format}') as tm";
            string sql = $"SELECT FORMAT(`tm`,'{format}') as _Time";
            (var columns, var tmpColumns, var columnIndexs) = GroupColumn(sensor.Configs, true, true);
            sql += columns;
            tmpSql += tmpColumns;

            tmpSql += " FROM datas WHERE Address =@Address)Y";
            sql = $"{sql} FROM {tmpSql} WHERE [tm] >= @Begin And [tm] <= @End GROUP BY FORMAT([tm], '{format}') ORDER BY FORMAT([tm], '{format}') DESC";
            Debug.WriteLine(sql);
            OleDbDataReader reader;
            ReturnData returnData;
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                Com.Parameters.Add(new OleDbParameter("@Address", sensor.Address));
                Com.Parameters.Add(new OleDbParameter("@Begin", OleDbType.DBDate) { Value = begin.ToString() });
                Com.Parameters.Add(new OleDbParameter("@End", OleDbType.DBDate) { Value = end.ToString() });
                reader = Com.ExecuteReader();
                returnData = new ReturnData(reader, columnIndexs);
                reader.Close();
            }
            return returnData;
        }

        /// <summary>
        /// 根据配置组列
        /// </summary>
        /// <param name="configs"></param>
        /// <param name="statistics">是否是统计</param> 
        /// <param name="hasHourOffset">是否有时间偏移</param>
        /// <returns>SQL SELECT 后的列, From后的列，列对应的寄存器地址</returns>
        private (string, string, List<int>) GroupColumn(List<DbConfig> configs, bool statistics = false, bool hasHourOffset = false)
        {
            string selectColumns = null;
            string formColumns = null;
            List<int> columnIndex = new List<int>();
            int i = 0;
            foreach (DbConfig cfg in configs)
            {
                if (statistics)
                {
                    if (cfg.Statistics.ToUpper() != "NOT")
                    {
                        selectColumns += String.Format(",{0}(A{1}) AS _A{1}", cfg.Statistics, cfg.Index);
                        if (hasHourOffset)
                        {
                            formColumns += $",A{cfg.Index}";
                        }
                        columnIndex.Add(i);
                    }
                }
                else
                {
                    selectColumns += String.Format(",A{0}", cfg.Index);
                    columnIndex.Add(i);
                }
                i++;
            }
            return (selectColumns, formColumns, columnIndex);
        }

        /// <summary>
        /// Values清空，Reader转换为List<Dictionary<uint, double?>>保存到Values
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="sensor"></param>
        /// <param name="statistics">是统计</param>
        private List<Dictionary<UInt16, double?>> ReaderToList(OleDbDataReader reader, DbSensor sensor, bool statistics = false)
        {
            List<Dictionary<UInt16, double?>> ls = new List<Dictionary<UInt16, double?>>();
            double? value;

            while (reader.Read())
            {
                Dictionary<UInt16, double?> dic = new Dictionary<UInt16, double?>();
                if (statistics)
                {
                    DateTime time = DateTime.Parse(reader.GetString(reader.GetOrdinal("_Time")));//分组后time是字符串
                    dic.Add(0, time.Ticks);
                }
                else
                {
                    dic.Add(0, reader.GetDateTime(reader.GetOrdinal("Time")).Ticks);//time是时间格式
                }
                foreach (var c in sensor.Configs)
                {
                    if (c.Index < 3) { continue; }
                    if (statistics)
                    {
                        if (c.Statistics.ToUpper() != "NOT")
                        {
                            var tmp = reader.GetValue(reader.GetOrdinal("_A" + c.Index));
                            if (tmp != DBNull.Value)
                                value = Math.Round((double)tmp, c.Digits + 1);
                            else
                                value = null;
                            dic.Add((ushort)c.Index, value);
                        }
                    }
                    else
                    {
                        var tmp = reader.GetValue(reader.GetOrdinal("A" + c.Index)); //要素字段
                        value = (tmp == DBNull.Value) ? null : (double?)tmp;
                        dic.Add((ushort)c.Index, value);
                    }
                }
                ls.Add(dic);
            }
            reader.Close();
            return ls;
        }

        bool IDB.SaveHistory(Weathers weathers)
        {
            bool result = false;
            string column = "Address";
            string valuse = "@Address";
            List<OleDbParameter> ls = new List<OleDbParameter>();
            OleDbParameter p = new OleDbParameter("@Address", weathers.Address);
            ls.Add(p);
            foreach (var weather in weathers.Values.Values)
            {
                string tmp;
                if (weather.Config.Type.ToLower() == CoreGlobal.Const_time)
                {
                    tmp = "Time";
                    column += ",[" + tmp + "]";
                    valuse += ",@" + tmp;
                    p = new OleDbParameter("@Time", OleDbType.Date)
                    {
                        Value = new DateTime((long)weather.Value)
                    };
                }
                else
                {
                    tmp = "A" + weather.Config.Index;
                    column += "," + tmp;
                    valuse += ",@" + tmp;
                    p = new OleDbParameter("@" + tmp, tmp);
                    if (weather.Value.HasValue)
                        p.Value = weather.Value;
                    else
                        p.Value = DBNull.Value;
                }
                ls.Add(p);
            }
            string sql = string.Format("INSERT INTO Datas({0}) VALUES({1}) ", column, valuse);
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();
                foreach (var q in ls)
                {
                    Com.Parameters.Add(q);
                }
                //Debug.WriteLine(sql);
                try
                {
                    result = Com.ExecuteNonQuery() > 0;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 检查表中字段是否存在
        /// </summary>
        /// <param name="sTblName"></param>
        /// <param name="sFldName"></param>
        /// <returns></returns>
        public bool CheckField(String sTblName, String sFldName)
        {
            bool isExist = false;
            try
            {
                object[] oa = { null, null, sTblName, sFldName };
                DataTable schemaTable;
                lock (Con)
                {
                    schemaTable = Con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, oa);
                }
                if (schemaTable.Rows.Count == 0)
                {
                    isExist = false;
                }
                else
                {
                    isExist = true;
                }
                Debug.WriteLine($"{sTblName}表中{sFldName}字段{(schemaTable.Rows.Count == 0 ? "不存在" : "存在")}。");
            }
            catch (Exception err)
            {
                Debug.WriteLine(err.Message);
            }

            return isExist;
        }


        /// <summary>
        /// 为一个表新增加一个字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="dataType">数据类型</param>
        /// /// <param name="fieldDef">字段定义</param>
        public bool AddColumnToTable(String tableName, String fieldName, String dataType, string fieldDef = "")
        {
            bool ret = false;
            string sqlAlter = $"alter table {tableName} add column {fieldName} {dataType} {fieldDef};";
            OleDbCommand com = new OleDbCommand(sqlAlter, Con);
            try
            {
                com.ExecuteNonQuery();
                ret = true;
                Debug.WriteLine("向数据库中的" + tableName + "表添加字段" + fieldName + "成功");
            }
            catch (Exception err)
            {
                Debug.WriteLine(err.Message);
                Debug.WriteLine("向数据库中的" + tableName + "表添加字段" + fieldName + "失败");
            }
            return ret;
        }

        void IDB.SetWarning(uint adress, uint index, double? max, double? min)
        {
            string sql = "UPDATE Configs SET [Max]=@Max,[Min]=@Min WHERE [address]=@Address AND [index]=@Index";
            lock (Con)
            {
                Com.CommandText = sql;
                Com.Parameters.Clear();

                if (max.HasValue)
                {
                    Com.Parameters.Add(new OleDbParameter("@Max", max));
                }
                else
                {
                    Com.Parameters.Add(new OleDbParameter("@Max", DBNull.Value));
                }

                if (min.HasValue)
                {
                    Com.Parameters.Add(new OleDbParameter("@Min", min));
                }
                else
                {
                    Com.Parameters.Add(new OleDbParameter("@Min", DBNull.Value));
                }

                Com.Parameters.Add(new OleDbParameter("@Address", adress));
                Com.Parameters.Add(new OleDbParameter("@Index", index));
                Debug.WriteLine(Com.CommandText);
                try
                {
                    object result = Com.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }
            }
            //return result == 1;
        }

        public static object SqlNull(object o)
        {
            return o ?? DBNull.Value;
        }



        /// <summary>
        /// 检测字段，如不存在则增加
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="field">字段</param>
        /// <param name="type">字段类型</param>
        /// <param name="define">字段定义</param>
        /// <returns>有字段或增加成功</returns>
        public bool CheckAndAddColumn(string table, string field, string type, string define = null)
        {
            bool ret = false;
            lock (Con)
            {
                try
                {
                    string strStateSql = $"select * from {table}";
                    OleDbCommand stateCmd = new OleDbCommand(strStateSql, Con);
                    DataSet dt = new DataSet();
                    OleDbDataAdapter da = new OleDbDataAdapter(stateCmd);
                    da.Fill(dt);

                    if (dt.Tables[0].Columns[field] == null)
                    {
                        if (AddColumnToTable(table, field, type, define))
                        {  ret = true; }
                    }
                    else
                    {
                        ret = true;
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex);
                }
            }
            return ret;
        }

        public static bool MakeNewDatabase(string path, string pwd)
        {

            bool rst = false;
            string conStr;
            if (pwd == null)
                conStr = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path};Jet OLEDB:Engine Type=5";
            else
                conStr = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={path};Jet OLEDB:Database password={pwd};Jet OLEDB:Engine Type=5";
            Catalog catalog = new Catalog();
            dynamic database = null;
            try
            {
                database = catalog.Create(conStr);
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(database);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(catalog);
            }

            List<string> sqls = new List<string>()
            {
                "Create Table Setting ([Key] text(20) primary key,[Value] text(20));",
                "Create Table Sensors (Address int,Type text(20),Name text(20),primary key(Address,Type))",
                "Create Table Configs (Address int,[Index] int,[Type] text(20),[Display] text(20),[Unit] text(20),[Digits] int,[Statistics] text(20),[Max] double,[Min] double,primary key([Address],[Index]))"
            };
            string a = "";
            for (int i = 3; i <= 48; i++)
            {
                a += $"A{i} double,";
            }
            string createDatas = $"Create Table Datas (Address int,[Time] datetime,{a} primary key(Address,[Time]))";
            sqls.Add(createDatas);

            if (database != null)
            {
                OleDbConnection con = new OleDbConnection(conStr);
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                foreach (var sql in sqls)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                con.Close();
                rst = true;
            }
            return rst;
        }

        #region 单例模式
        private static readonly Access _access = new Access();

        //显式的静态构造函数用来告诉C#编译器在其内容实例化之前不要标记其类型
        static Access() { }
        //声明一个私有的构造方法，让外部无法调用这个类的构造方法
        //private Access() { }

        /// <summary>
        /// 返回对象单例
        /// </summary>
        public static Access Singleton
        {
            get { return _access; }
        }
        #endregion
    }
}
